with dwd_tab_arrival_base_dt as (
    select
         arr.waybill_no      as waybill_no      --运单号
        ,arr.scan_time       as scan_time       --扫描时间
        ,arr.scan_site_code  as scan_site_code  --扫描站点编码
        ,arr.scan_site       as scan_site_name  --扫描站点名称
        ,arr.input_time      as input_time      --录入时间
        ,arr.package_code    as package_code    --包号
        ,arr.scan_type       as scan_type       --扫描类型
        ,arr.dt              as dt              --分区日期
        ,nvl(arr.pre_station_code,send.scan_site_code) as pre_station_code  --上一站
    from jms_dwd.dwd_tab_barscan_arrival_base_dt arr
    left join (
        select *
        from (
            select
                  *,row_number() over(partition by waybill_no,next_station_code order by scan_time desc) as rk --按照最晚取，
            from jms_dwd.dwd_tab_barscan_send_base_dt --装车发件
            where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
              and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
              and !(length(waybill_no) = 13 and waybill_no like 'B%')
              and next_station_code is not null
        ) a where a.rk = 1
    ) send on arr.waybill_no = send.waybill_no    --关联发件取发件当前站 = 到件上一站
          and arr.scan_site_code = send.next_station_code
          and arr.scan_time > send.scan_time
     where arr.dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
       and to_date(arr.scan_time) between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
)


insert overwrite table jms_dm.dm_center_transfer_summary_dt
select
      scan_time --扫描时间
     ,scan_site_code        --中心code
     ,scan_site_name        --中心名称
     ,provider_id           --省份ID
     ,provider_desc         --省份名称
     ,city_id               --城市ID
     ,city_desc             --城市名称
     ,regional_id           --大区id
     ,regional_desc         --大区名称
     ,agent_code            --代理区code
     ,agent_name            --代理区名称
     ,order_source_code     --订单来源code
     ,order_source_name     --订单来源名称
     ,oper_shift            --操作班次
     ,nvl(sum(arrival_cnt         ),0) as arrival_cnt          --到件量
     ,nvl(sum(send_cnt            ),0) as send_cnt             --发件量
     ,nvl(sum(arrival_pack_cnt    ),0) as arrival_pack_cnt     --到包量
     ,nvl(sum(send_pack_cnt       ),0) as send_pack_cnt        --发包量
     ,nvl(sum(enterport_cnt       ),0) as enterport_cnt        --进港件量
     ,nvl(sum(same_city_cnt       ),0) as same_city_cnt        --同城件量
     ,nvl(sum(transfer_cnt        ),0) as transfer_cnt         --中转件量
     ,nvl(sum(leaveport_cnt       ),0) as leaveport_cnt        --出港件量
     ,nvl(sum(enterport_pack_cnt  ),0) as enterport_pack_cnt   --进港包量
     ,nvl(sum(same_city_pack_cnt  ),0) as same_city_pack_cnt   --同城包量
     ,nvl(sum(transfer_pack_cnt   ),0) as transfer_pack_cnt    --中转包量
     ,nvl(sum(leaveport_pack_cnt  ),0) as leaveport_pack_cnt   --出港包量
     ,nvl(sum(enterport_weight_tot),0) as enterport_weight_tot --进港重量汇总
     ,nvl(sum(same_city_weight_tot),0) as same_city_weight_tot --同城重量汇总
     ,nvl(sum(transfer_weight_tot ),0) as transfer_weight_tot  --中转重量汇总
     ,nvl(sum(leaveport_weight_tot),0) as leaveport_weight_tot --出港重量汇总
     ,nvl(sum(arrival_cnt) + sum(send_cnt),0) as trans_cnt     --转运货量/件
     ,nvl(sum(arrival_pack_cnt) + sum(send_pack_cnt),0) as trans_pack_cnt --转运货量/包
     ,nvl(sum(enterport_cnt) + sum(same_city_cnt) + sum(transfer_cnt) + sum(leaveport_cnt) + sum(other_cnt),0) as oper_cnt --操作件量
     ,nvl(sum(enterport_pack_cnt) + sum(same_city_pack_cnt) + sum(transfer_pack_cnt) + sum(leaveport_pack_cnt) + sum(other_pack_cnt),0) as oper_pack_cnt --操作包量
     ,nvl(sum(enterport_weight_tot) + sum(same_city_weight_tot) + sum(transfer_weight_tot) + sum(leaveport_weight_tot) + sum(other_weight_tot),0) as oper_weight_cnt --操作重量汇总
     ,nvl(sum(other_cnt)       ,0) as other_cnt        --其他件量
     ,nvl(sum(other_pack_cnt)  ,0) as other_pack_cnt   --其他包量
     ,nvl(sum(other_weight_tot),0) as other_weight_tot --其他重量
     ,null as sys_time
     ,null as effect_start_time
     ,null as effect_end_time
     ,null as shift_start_time
     ,null as shift_end_time
     ,scan_time as dt
from ( --转运货量
    select
          to_date(scan_time) as scan_time --扫描时间
         ,scan_site_code        --中心code
         ,scan_site_name        --中心名称
         ,provider_id           --省份ID
         ,provider_desc         --省份名称
         ,city_id               --城市ID
         ,city_desc             --城市名称
         ,regional_id           --大区id
         ,regional_desc         --大区名称
         ,agent_code            --代理区code
         ,agent_name            --代理区名称
         ,order_source_code     --订单来源code
         ,order_source_name     --订单来源名称
         ,oper_shift            --操作班次
         ,count(case when scan_type = 'arrival' then truns.waybill_no end) as arrival_cnt --到件量
         ,count(case when scan_type = 'send'    then truns.waybill_no end) as send_cnt    --发件量
         ,count(distinct case when scan_type = 'arrival' then truns.package_code end) as arrival_pack_cnt --到包量
         ,count(distinct case when scan_type = 'send'    then truns.package_code end) as send_pack_cnt    --发包量
         ,0 as enterport_cnt --进港件量
         ,0 as same_city_cnt --同城件量
         ,0 as transfer_cnt  --中转件量
         ,0 as leaveport_cnt --出港件量
         ,0 as other_cnt     --其他件量
         ,0 as enterport_pack_cnt --进港包量
         ,0 as same_city_pack_cnt --同城包量
         ,0 as transfer_pack_cnt  --中转包量
         ,0 as leaveport_pack_cnt --出港包量
         ,0 as other_pack_cnt     --其他包量
         ,0 as enterport_weight_tot --进港重量汇总
         ,0 as same_city_weight_tot --同城重量汇总
         ,0 as transfer_weight_tot  --中转重量汇总
         ,0 as leaveport_weight_tot --出港重量汇总
         ,0 as other_weight_tot     --其他重量
    from(
         select
               send.waybill_no           as   waybill_no        --运单号
              ,case when send.scan_time >= concat(to_date(send.scan_time),' ',oper.shift_start_time) then date_add(to_date(send.scan_time),1) --班次维护开始时间  
                    when send.scan_time <= concat(to_date(send.scan_time),' ',oper.shift_end_time)   then to_date(send.scan_time)             --班次维护结束时间
                    else to_date(send.scan_time) end as scan_time
              ,send.scan_site_code       as   scan_site_code    --网点编码
              ,send.scan_site_name       as   scan_site_name    --网点名称
              ,send.package_code         as   package_code      --包号
              ,send.scan_type            as   scan_type         --扫描类型
              ,dim.provider_id           as   provider_id       --省份ID
              ,dim.provider_desc         as   provider_desc     --省份名称
              ,dim.city_id               as   city_id           --城市ID
              ,dim.city_desc             as   city_desc         --城市名称
              ,dim.manage_code           as   regional_id       --管理大区id
              ,dim.manage_name           as   regional_desc     --管理大区名称
              ,dim.agent_code            as   agent_code        --代理区code
              ,dim.agent_name            as   agent_name        --代理区名称
              ,der.order_source_code     as   order_source_code --订单来源code
              ,der.order_source_name     as   order_source_name --订单来源名称
              ,concat(substr(oper.shift_start_time,1,5),'-',substr(oper.shift_end_time,1,5)) as oper_shift
              ,row_number() over (partition by send.waybill_no,send.scan_site_code,send.scan_type order by send.scan_time desc) as rn
         from (
              select
                    waybill_no       as waybill_no        --运单号
                   ,scan_time       as scan_time         --扫描时间
                   ,scan_site_code  as scan_site_code    --站点
                   ,scan_site       as scan_site_name    --网点名称
                   ,package_code    as package_code      --包号
                   ,'send'          as scan_type         --扫描类型
              from jms_dwd.dwd_tab_barscan_centersend_base_dt --中心发件表
              where dt between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
                and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
                and !(length(waybill_no) = 13 and waybill_no like 'B%')
              union all
              select
                    waybill_no       as waybill_no       --运单号
                   ,scan_time       as scan_time        --扫描时间
                   ,scan_site_code  as scan_site_code   --站点
                   ,scan_site       as scan_site_name   --网点名称
                   ,package_code    as package_code     --包号
                   ,'arrival'       as scan_type        --扫描类型
              from jms_dwd.dwd_tab_barscan_centerarrival_base_dt --中心到件表
              where dt between  date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
                and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
                and !(length(waybill_no) = 13 and waybill_no like 'B%')
         ) send
         left join jms_dim.dim_network_whole_massage dim  --网点宽表
            on dim.code = send.scan_site_code
         left join (
             select
                  transport_center_code
                 ,transport_center_name
                 ,effect_start_time
                 ,effect_end_time
                 ,modify_by
                 ,modify_time
                 ,status_code
                 ,substr(shift_start_time,12,8) as shift_start_time  --班次维护开始时间
                 ,substr(shift_end_time,12,8) as shift_end_time      --班次维护结束时间
                 ,if(substr(shift_start_time,12,8) > substr(shift_end_time,12,8),1,0) as span_day
             from jms_dwd.dwd_tab_report_shift_operate_base --班次维护表
         ) oper on oper.transport_center_code = send.scan_site_code
               and oper.status_code = 2
               and to_date(send.scan_time) between date_sub(to_date(oper.effect_start_time),3) and date_sub(to_date(oper.effect_end_time),3)  --班次生效时间-失效时间
         left join (
             select
                   waybill_no
                  ,case when ordersource_code in ('1085','D67')         then ordersource_code
                        when ordersource_code in ('1133','D190')        then ordersource_code
                        when ordersource_code in ('JD')                 then ordersource_code
                        when ordersource_code in ('1146','D420')        then ordersource_code
                        when ordersource_code in ('SN','D501')          then ordersource_code
                        when ordersource_code in ('D553')               then ordersource_code
                        when ordersource_code in ('D573')               then ordersource_code
                        when ordersource_code in ('D09', 'D60','D69')   then ordersource_code
                        else '其他' end as order_source_code
                  ,case when ordersource_name = '紫金山'   then '紫金山'
                        when ordersource_name = '逍遥峰'   then '逍遥峰'
                        when ordersource_name = '极地湾'   then '极地湾'
                        when ordersource_name = '麦田圈'   then '麦田圈'
                        when ordersource_name = '苏宁'     then '苏宁'
                        when ordersource_name = '有赞'     then '有赞'
                        when ordersource_name = '七星潭'    then '七星潭'
                        when ordersource_name = '桃花岛'    then '桃花岛'
                        else '其他' end as order_source_name
             from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt  --取近45天订单来源
             where dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'  --取21天
         ) der on der.waybill_no = send.waybill_no
          where to_date(send.scan_time) between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
    ) truns where truns.rn = 1
    group by to_date(scan_time)    --扫描时间
           ,scan_site_code        --中心code
           ,scan_site_name        --中心名称
           ,provider_id           --省份ID
           ,provider_desc         --省份名称
           ,city_id               --城市ID
           ,city_desc             --城市名称
           ,regional_id           --大区id
           ,regional_desc         --大区名称
           ,agent_code            --代理区code
           ,agent_name            --代理区名称
           ,order_source_code     --订单来源code
           ,order_source_name     --订单来源名称
           ,oper_shift            --操作班次
    union all
    ----操作量
    select
          to_date(scan.scan_date) as scan_time --扫描时间
         ,scan.scan_site_code     --中心code
         ,scan.scan_site_name     --中心名称
         ,scan.provider_id        --省份ID
         ,scan.provider_desc      --省份名称
         ,scan.city_id            --城市ID
         ,scan.city_desc          --城市名称
         ,scan.regional_id        --大区id
         ,scan.regional_desc      --大区名称
         ,scan.agent_code         --代理区code
         ,scan.agent_name         --代理区名称
         ,scan.order_source_code  --订单来源code
         ,scan.order_source_name  --订单来源名称
         ,scan.oper_shift         --操作班次
         ,0 as arrival_cnt        --到件量
         ,0 as send_cnt           --发件量
         ,0 as arrival_pack_cnt   --到包量
         ,0 as send_pack_cnt      --发包量
         ,count(case when scan.type = 1 and !(length(scan.waybill_no) = 13 and scan.waybill_no like 'B%') then scan.waybill_no end) as enterport_cnt --进港件量
         ,count(case when scan.type = 2 and !(length(scan.waybill_no) = 13 and scan.waybill_no like 'B%') then scan.waybill_no end) as same_city_cnt --同城件量
         ,count(case when scan.type = 3 and !(length(scan.waybill_no) = 13 and scan.waybill_no like 'B%') then scan.waybill_no end) as transfer_cnt  --中转件量
         ,count(case when scan.type = 4 and !(length(scan.waybill_no) = 13 and scan.waybill_no like 'B%') then scan.waybill_no end) as leaveport_cnt --出港件量
         ,count(case when scan.type = 0 and !(length(scan.waybill_no) = 13 and scan.waybill_no like 'B%') then scan.waybill_no end) as other_cnt     --其他件量
         ,count(distinct case when scan.type = 1 then scan.package_code end) as enterport_pack_cnt --进港包量
         ,count(distinct case when scan.type = 2 then scan.package_code end) as same_city_pack_cnt --同城包量
         ,count(distinct case when scan.type = 3 then scan.package_code end) as transfer_pack_cnt  --中转包量
         ,count(distinct case when scan.type = 4 then scan.package_code end) as leaveport_pack_cnt --出港包量
         ,count(distinct case when scan.type = 0 then scan.package_code end) as other_pack_cnt     --其他包量
         ,sum(case when scan.type=1 and nvl(scan.first_center_max_weight, 0) <> 0 then scan.first_center_max_weight  --始中心最大重量
                   when scan.type=1 and nvl(scan.first_nodal_max_weight , 0) <> 0 then scan.first_nodal_max_weight   --始集散最大重量
                   when scan.type=1 and nvl(scan.warehousing_max_weight , 0) <> 0 then scan.warehousing_max_weight   --最大入仓重量
                   when scan.type=1 and nvl(scan.taking_max_weight      , 0) <> 0 then scan.taking_max_weight        --最大揽收重量
                   else 0 end) as enterport_weight_tot
         ,sum(case when scan.type=2 and nvl(scan.first_center_max_weight, 0) <> 0 then scan.first_center_max_weight
                   when scan.type=2 and nvl(scan.first_nodal_max_weight , 0) <> 0 then scan.first_nodal_max_weight
                   when scan.type=2 and nvl(scan.warehousing_max_weight , 0) <> 0 then scan.warehousing_max_weight
                   when scan.type=2 and nvl(scan.taking_max_weight      , 0) <> 0 then scan.taking_max_weight
                   else 0 end) as same_city_weight_tot
         ,sum(case when scan.type=3 and nvl(scan.first_center_max_weight, 0) <> 0 then scan.first_center_max_weight
                   when scan.type=3 and nvl(scan.first_nodal_max_weight , 0) <> 0 then scan.first_nodal_max_weight
                   when scan.type=3 and nvl(scan.warehousing_max_weight , 0) <> 0 then scan.warehousing_max_weight
                   when scan.type=3 and nvl(scan.taking_max_weight      , 0) <> 0 then scan.taking_max_weight
                   else 0 end) as transfer_weight_tot
         ,sum(case when scan.type=4 and nvl(scan.first_center_max_weight, 0) <> 0 then scan.first_center_max_weight
                   when scan.type=4 and nvl(scan.first_nodal_max_weight , 0) <> 0 then scan.first_nodal_max_weight
                   when scan.type=4 and nvl(scan.warehousing_max_weight , 0) <> 0 then scan.warehousing_max_weight
                   when scan.type=4 and nvl(scan.taking_max_weight      , 0) <> 0 then scan.taking_max_weight
                   else 0 end) as leaveport_weight_tot
         ,sum(case when scan.type=0 and nvl(scan.first_center_max_weight, 0) <> 0 then scan.first_center_max_weight
                   when scan.type=0 and nvl(scan.first_nodal_max_weight , 0) <> 0 then scan.first_nodal_max_weight
                   when scan.type=0 and nvl(scan.warehousing_max_weight , 0) <> 0 then scan.warehousing_max_weight
                   when scan.type=0 and nvl(scan.taking_max_weight      , 0) <> 0 then scan.taking_max_weight
                   else 0 end) as other_weight_tot
    from (
        select *,row_number() over(partition by waybill_no,scan_site_code order by scan_time desc) as rk
        from( 
            --到件
            select
                 centerarr.waybill_no       as   waybill_no        --运单号
                ,case when centerarr.scan_time >= concat(to_date(centerarr.scan_time),' ',oper.shift_start_time) then date_add(to_date(centerarr.scan_time),1) --班次维护开始时间  
                      when centerarr.scan_time <= concat(to_date(centerarr.scan_time),' ',oper.shift_end_time)   then to_date(centerarr.scan_time)             --班次维护结束时间
                      else to_date(centerarr.scan_time) end as scan_date
                ,centerarr.scan_time        as   scan_time
                ,centerarr.scan_site_code   as   scan_site_code    --站点
                ,centerarr.scan_site_name   as   scan_site_name    --站点
                ,centerarr.package_code     as   package_code      --包号
                ,dim.provider_id            as   provider_id       --省份ID
                ,dim.provider_desc          as   provider_desc     --省份名称
                ,dim.city_id                as   city_id           --城市ID
                ,dim.city_desc              as   city_desc         --城市名称
                ,dim.manage_code            as   regional_id       --大区id
                ,dim.manage_name            as   regional_desc     --大区名称
                ,dim.agent_code             as   agent_code        --代理区code
                ,dim.agent_name             as   agent_name        --代理区名称
                ,concat(substr(oper.shift_start_time,1,5),'-',substr(oper.shift_end_time,1,5)) as oper_shift
                ,case when a.network_type = 4 and b.network_type <>4 then 1  --1:进港
                      when a.network_type <>4 and b.network_type <>4 then 2  --2:同城
                      when a.network_type = 4 and b.network_type = 4 then 3  --3:中转
                      when a.network_type <>4 and b.network_type = 4 then 4  --4:出港
                      else 0 end as type
                ,der.order_source_code        --订单来源code
                ,der.order_source_name        --订单来源名称
                ,wide.taking_max_weight       --最大揽收重量
                ,wide.warehousing_max_weight  --最大入仓重量
                ,wide.first_center_max_weight --始中心最大重量
                ,wide.first_nodal_max_weight  --始集散最大重量
                ,row_number() over(partition by centerarr.waybill_no,centerarr.scan_site_code order by centerarr.scan_time asc) as rn
            from  dwd_tab_arrival_base_dt centerarr --中心到件
            left join (
               select
                     waybill_no
                    ,scan_time
                    ,scan_site_code
                    ,scan_site_name
                    ,pre_station_code
               from dwd_tab_arrival_base_dt --到件表
               where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
                 and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
            ) arr on arr.waybill_no = centerarr.waybill_no
                 and arr.scan_time > centerarr.scan_time
                 and arr.pre_station_code = centerarr.scan_site_code
            left join jms_dim.dim_network_whole_massage a --上一站
              on a.code = centerarr.pre_station_code
            left join jms_dim.dim_network_whole_massage b --下一站
              on b.code = arr.scan_site_code
            left join jms_dim.dim_network_whole_massage dim --当前站
              on dim.code = centerarr.scan_site_code
            left join (
                select
                    transport_center_code
                   ,transport_center_name
                   ,effect_start_time
                   ,effect_end_time
                   ,modify_by
                   ,modify_time
                   ,status_code
                   ,substr(shift_start_time,12,8) as shift_start_time  --班次维护开始时间
                   ,substr(shift_end_time,12,8) as shift_end_time      --班次维护结束时间
                   ,if(substr(shift_start_time,12,8) > substr(shift_end_time,12,8),1,0) as span_day
                from jms_dwd.dwd_tab_report_shift_operate_base --班次维护表
            ) oper on oper.transport_center_code = centerarr.scan_site_code
                  and oper.status_code = 2
                  and to_date(centerarr.scan_time) between date_sub(to_date(oper.effect_start_time),3) and date_sub(to_date(oper.effect_end_time),3)   --班次生效时间-失效时间
            left join (
               select
                     waybill_no
                    ,case when ordersource_code in ('1085','D67')         then ordersource_code
                          when ordersource_code in ('1133','D190')        then ordersource_code
                          when ordersource_code in ('JD')                 then ordersource_code
                          when ordersource_code in ('1146','D420')        then ordersource_code
                          when ordersource_code in ('SN','D501')          then ordersource_code
                          when ordersource_code in ('D553')               then ordersource_code
                          when ordersource_code in ('D573')               then ordersource_code
                          when ordersource_code in ('D09', 'D60','D69')   then ordersource_code
                          else '其他' end as order_source_code
                  ,case when ordersource_name = '紫金山'   then '紫金山'
                        when ordersource_name = '逍遥峰'   then '逍遥峰'
                        when ordersource_name = '极地湾'   then '极地湾'
                        when ordersource_name = '麦田圈'   then '麦田圈'
                        when ordersource_name = '苏宁'     then '苏宁'
                        when ordersource_name = '有赞'     then '有赞'
                        when ordersource_name = '七星潭'    then '七星潭'
                        when ordersource_name = '桃花岛'    then '桃花岛'
                        else '其他' end as order_source_name
               from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt  --取近45天订单来源
               where dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'  --取21天
            ) der on centerarr.waybill_no = der.waybill_no
            left join (
               select
                   waybill_no
                    ,taking_max_weight       --最大揽收重量
                    ,warehousing_max_weight  --最大入仓重量
                    ,first_center_max_weight --始中心最大重量
                    ,first_nodal_max_weight  --始集散最大重量
               from jms_dwd.dwd_wide_unsign_summary_waybill_dt
               where dt between date_sub('{{ execution_date | cst_ds }}',14) and '{{ execution_date | cst_ds }}' --取14天
            ) wide on wide.waybill_no = centerarr.waybill_no
            where centerarr.dt between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
              and to_date(centerarr.scan_time) between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
              and centerarr.scan_type = '中心到件'
            union all --发件
            select
                  centsend.waybill_no       as   waybill_no        --运单号
                 ,case when centsend.scan_time >= concat(to_date(centsend.scan_time),' ',oper.shift_start_time) then date_add(to_date(centsend.scan_time),1) --班次维护开始时间  
                       when centsend.scan_time <= concat(to_date(centsend.scan_time),' ',oper.shift_end_time)   then to_date(centsend.scan_time)             --班次维护结束时间
                       else to_date(centsend.scan_time) end as scan_date
                 ,centsend.scan_time        as   scan_time
                 ,centsend.scan_site_code   as   scan_site_code    --站点
                 ,centsend.scan_site        as   scan_site_name    --站点
                 ,centsend.package_code     as   package_code      --包号
                 ,dim.provider_id           as   provider_id       --省份ID
                 ,dim.provider_desc         as   provider_desc     --省份名称
                 ,dim.city_id               as   city_id           --城市ID
                 ,dim.city_desc             as   city_desc         --城市名称
                 ,dim.manage_code           as   regional_id       --大区id
                 ,dim.manage_name           as   regional_desc     --大区名称
                 ,dim.agent_code            as   agent_code        --代理区code
                 ,dim.agent_name            as   agent_name        --代理区名称
                 ,concat(substr(oper.shift_start_time,1,5),'-',substr(oper.shift_end_time,1,5)) as oper_shift
                 ,case when a.network_type = 4 and b.network_type <>4 then 1  --1:进港
                       when a.network_type <>4 and b.network_type <>4 then 2  --2:同城
                       when a.network_type = 4 and b.network_type = 4 then 3  --3:中转
                       when a.network_type <>4 and b.network_type = 4 then 4  --4:出港
                       else 0 end as type
                 ,der.order_source_code        --订单来源code
                 ,der.order_source_name        --订单来源名称
                 ,wide.taking_max_weight       --最大揽收重量
                 ,wide.warehousing_max_weight  --最大入仓重量
                 ,wide.first_center_max_weight --始中心最大重量
                 ,wide.first_nodal_max_weight  --始集散最大重量
                 ,row_number() over(partition by centsend.waybill_no,centsend.scan_site_code order by centsend.scan_time desc) as rn
            from jms_dwd.dwd_tab_barscan_centersend_base_dt centsend
            left join (
                select waybill_no,scan_time,scan_site_code,next_station_code
                from jms_dwd.dwd_tab_barscan_send_base_dt
                where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
                  and to_date(scan_time) between  date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
            ) send on send.waybill_no = centsend.waybill_no
                and send.scan_time  < centsend.scan_time
                and send.next_station_code = centsend.scan_site_code
            left join jms_dim.dim_network_whole_massage a --上一站
               on a.code = send.scan_site_code
            left join jms_dim.dim_network_whole_massage b --下一站
               on b.code = centsend.next_station_code
            left join jms_dim.dim_network_whole_massage dim --当前站
               on dim.code = centsend.scan_site_code
            left join (
                select
                    transport_center_code
                   ,transport_center_name
                   ,effect_start_time
                   ,effect_end_time
                   ,modify_by
                   ,modify_time
                   ,status_code
                   ,substr(shift_start_time,12,8) as shift_start_time  --班次维护开始时间
                   ,substr(shift_end_time,12,8) as shift_end_time      --班次维护结束时间
                   ,if(substr(shift_start_time,12,8) > substr(shift_end_time,12,8),1,0) as span_day
                from jms_dwd.dwd_tab_report_shift_operate_base --班次维护表
            ) oper on oper.transport_center_code = centsend.scan_site_code
                  and oper.status_code = 2
                  and to_date(centsend.scan_time) between date_sub(to_date(oper.effect_start_time),3) and date_sub(to_date(oper.effect_end_time),3)   --班次生效时间-失效时间
            left join (
                select
                      waybill_no
                     ,case when ordersource_code in ('1085','D67')         then ordersource_code
                           when ordersource_code in ('1133','D190')        then ordersource_code
                           when ordersource_code in ('JD')                 then ordersource_code
                           when ordersource_code in ('1146','D420')        then ordersource_code
                           when ordersource_code in ('SN','D501')          then ordersource_code
                           when ordersource_code in ('D553')               then ordersource_code
                           when ordersource_code in ('D573')               then ordersource_code
                           when ordersource_code in ('D09', 'D60','D69')   then ordersource_code
                           else '其他' end as order_source_code
                    ,case when ordersource_name = '紫金山'   then '紫金山'
                          when ordersource_name = '逍遥峰'   then '逍遥峰'
                          when ordersource_name = '极地湾'   then '极地湾'
                          when ordersource_name = '麦田圈'   then '麦田圈'
                          when ordersource_name = '苏宁'     then '苏宁'
                          when ordersource_name = '有赞'     then '有赞'
                          when ordersource_name = '七星潭'    then '七星潭'
                          when ordersource_name = '桃花岛'    then '桃花岛'
                          else '其他' end as order_source_name
                from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt  --取近45天订单来源
                where dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'  --取45天
            ) der on centsend.waybill_no = der.waybill_no
            left join (
                select
                      waybill_no
                     ,taking_max_weight       --最大揽收重量
                     ,warehousing_max_weight  --最大入仓重量
                     ,first_center_max_weight --始中心最大重量
                     ,first_nodal_max_weight  --始集散最大重量
                from jms_dwd.dwd_wide_unsign_summary_waybill_dt
                where dt between date_sub('{{ execution_date | cst_ds }}',14) and '{{ execution_date | cst_ds }}' --取14天
            ) wide on wide.waybill_no = centsend.waybill_no
            where centsend.dt between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
              and to_date(centsend.scan_time) between date_sub('{{ execution_date | cst_ds }}',3) and '{{ execution_date | cst_ds }}'
        ) a where a.rn = 1 
    ) scan where scan.rk = 1
    group by to_date(scan.scan_date) --扫描时间
           ,scan.scan_site_code     --网点code
           ,scan.scan_site_name     --网点名称
           ,scan.provider_id        --省份ID
           ,scan.provider_desc      --省份名称
           ,scan.city_id            --城市ID
           ,scan.city_desc          --城市名称
           ,scan.regional_id        --大区id
           ,scan.regional_desc      --大区名称
           ,scan.agent_code         --代理区code
           ,scan.agent_name         --代理区名称
           ,scan.order_source_code  --订单来源code
           ,scan.order_source_name  --订单来源名称
           ,scan.oper_shift         --操作班次
) a group by scan_time --扫描时间
            ,scan_site_code        --中心code
            ,scan_site_name        --中心名称
            ,provider_id           --省份ID
            ,provider_desc         --省份名称
            ,city_id               --城市ID
            ,city_desc             --城市名称
            ,regional_id           --大区id
            ,regional_desc         --大区名称
            ,agent_code            --代理区code
            ,agent_name            --代理区名称
            ,order_source_code     --订单来源code
            ,order_source_name     --订单来源名称
            ,oper_shift            --操作班次
distribute by dt,pmod(hash(rand()),10);